
//start with transaction level files 
use ../Intermediate/transaction_clean_loans, clear
append using ../Intermediate/transaction_clean_bonds, gen(src)
gen source = "Dealscan" if source_ds == 1
replace source = "IJ Global" if source_ds == 0
replace source = "SDC" if src == 1
drop source_ds src
replace bond_ind = 1 if source == "SDC"

//make a new transaction id 
egen transaction_id = group(transaction_loan_id transaction_bond_id), missing

//save transaciton id link
preserve
keep transaction_id transaction_loan_id transaction_bond_id
duplicates drop
tempfile translink 
save `translink'
restore 

//keep cleaning up 
drop transaction_bond_id transaction_loan_id
order transaction_id
order sdc_id, after(TrancheId)
gen coupon_bps = 100*coupon
order coupon_bps, after(spread_bps)
drop coupon 
replace obs_ammendment = 0 if source == "SDC"

compress 
save ../Intermediate/transaction_clean, replace


//now build transaction by lender files
use ../Intermediate/transaction_lender_clean_bonds, clear
joinby transaction_bond_id using `translink'
unique transaction_id BankID
tempfile tb
save `tb'

use ../Intermediate/transaction_lender_clean_loans, clear
joinby transaction_loan_id using `translink'
unique transaction_id BankID
append using `tb', gen(src_sdc)

duplicates report transaction_id BankID 

drop transaction_loan_id transaction_bond_id
order transaction_id 
replace lead_bank = 1 if src_sdc == 1
drop src_sdc
compress

merge m:1 BankID using ../Raw/Bank_Sample/BankSample_List, nogen keep(1 3) 


save ../Intermediate/transaction_lender_clean, replace
